<?php
require_once('config.inc.php');

class DatabaseManager
{
	private $connection = FALSE;
	
	function getConnection()
	{
		if(!($this->connection))
		{
			$this->connection = mysql_connect(DB_SERVER,DB_USER_NAME,DB_USER_PASSWORD);
		}
		return $this->connection;
	}
	
	function createDB()
	{
		if (!($con = $this->getConnection()))
	  	{
	  		die('Could not connect: ' . mysql_error());
	  	}
	  	else
	  	{
			if (mysql_query("CREATE DATABASE ".DB_NAME,$con))
	  		{
	  			echo "Database created";
	  		}
			else
	  		{
	  			//echo "Error creating database: " . mysql_error();
	  		}
	  		mysql_select_db(DB_NAME);
	  		
	  		$sql = "create table if not exists userdata(user_id INTEGER PRIMARY KEY AUTO_INCREMENT";
	  		$sql .= ", firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50) NOT NULL, address VARCHAR(150)";
	  		$sql .= ", postcode INTEGER, city VARCHAR(100), country VARCHAR(50), phone VARCHAR(50)";
	  		$sql .= ", employer VARCHAR(100), mail VARCHAR(50), title VARCHAR(20), homepage VARCHAR(150), position VARCHAR(50))";
	  		$result = mysql_query($sql);
	  		
	  		$sql = "create table if not exists adresses(adresses_id INTEGER PRIMARY KEY AUTO_INCREMENT";
	  		$sql .= ", user_id INTEGER";
	  		$sql .= ", firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50) NOT NULL, address VARCHAR(150)";
	  		$sql .= ", postcode INTEGER, city VARCHAR(100), country VARCHAR(50), phone VARCHAR(50)";
	  		$sql .= ", company VARCHAR(100), mail VARCHAR(50), title VARCHAR(20), FOREIGN KEY(user_id) REFERENCES userdata(user_id))";
	  		mysql_query($sql);
	  		
	  		$sql = "create table if not exists files(file_id INTEGER PRIMARY KEY AUTO_INCREMENT";
	  		$sql .= ", template_name VARCHAR(50) NOT NULL, category_id INTEGER, file_path VARCHAR(250) NOT NULL, FOREIGN KEY(category_id) REFERENCES categories(category_id))";
	  		mysql_query($sql);
	  		
	  		$sql = "create table if not exists categories(category_id INTEGER PRIMARY KEY AUTO_INCREMENT";
	  		$sql .= ", category_name VARCHAR(50) NOT NULL)";
	  		mysql_query($sql);
	  		
	  		/* new table metadata*/ 
        $sql = "create table if not exists metadata(meta_id INTEGER PRIMARY KEY AUTO_INCREMENT";
        $sql .= ", user_id INTEGER NOT NULL";
	  		$sql .= ", title VARCHAR(50) NOT NULL, author VARCHAR(50) NOT NULL, creation_date DATE NOT NULL";
	  		$sql .= ", last_edit DATE NOT NULL, description VARCHAR(150), tag1 VARCHAR(30), tag2 VARCHAR(30)";
	  		$sql .= ", tag3 VARCHAR(30), tag4 VARCHAR(30), tag5 VARCHAR(30)";
	  		$sql .= ", FOREIGN KEY(user_id) REFERENCES userdata(user_id))";
	  		mysql_query($sql);
	  		
	  		$sql = "INSERT INTO `categories` (`category_id` ,`category_name`)";
	  		$sql .= "VALUES (1 , 'Latex');";
	  		mysql_query($sql);
	  		$sql = "INSERT INTO `categories` (`category_id` ,`category_name`)";
	  		$sql .= "VALUES (2 , 'Docx');";
	  		mysql_query($sql);
	  	}
	}
	
	function templates_in_category($category)
	{
		if (($con = $this->getConnection()))
	  	{
	  		$sql = "SELECT templete_name WHERE category = " .$category;
	  		$result = mysql_query($sql);
	  		return $result;
	  	}
	  	else return NULL;
	}
}
?>